﻿Imports System.Data.SQLite

Namespace Database
    Public Class clsDatabase
        Private m_sConnectionString As String
        Private m_pConnection As SQLiteConnection
        Private m_pCommand As SQLiteCommand

        Private Const TABLENAME_ID As String = "SysId"

        Private Shared m_pOpenDatabases As New Dictionary(Of String, clsdatabase)

        Private Sub New()
        End Sub

#Region " Properties "
        Public ReadOnly Property ConnectionString As String
            Get
                Return m_sConnectionString
            End Get
        End Property
        Public Property Connection As SQLiteConnection
            Get
                If m_pConnection Is Nothing Then
                    m_pConnection = New SQLiteConnection(m_sConnectionString)
                    m_pConnection.Open()
                End If
                Return m_pConnection
            End Get
            Set(ByVal value As SQLiteConnection)
                m_pConnection = value
            End Set
        End Property
        Public Property Command As SQLiteCommand
            Get
                If m_pCommand Is Nothing Then
                    m_pCommand = New SQLiteCommand(Connection)
                End If
                Return m_pCommand
            End Get
            Set(ByVal value As SQLiteCommand)
                m_pCommand = value
            End Set
        End Property
        Private ReadOnly Property DefaultFields As Dictionary(Of String, clsAttribute)
            Get
                Dim pOutList As New Dictionary(Of String, clsAttribute)
                pOutList.Add("CreationDate", New clsAttribute("CreationDate", "DATETIME"))
                pOutList.Add("ModificationDate", New clsAttribute("ModificationDate", "DATETIME"))
                Return pOutList
            End Get
        End Property
#End Region

        Public Function CreateTable(ByVal sTableName As String, ByVal pAttributeList As List(Of clsAttribute)) As Boolean
            If TableExists(sTableName) Then Return True
            Dim sStatement As String = String.Empty

            For Each pDefAtt In DefaultFields
                pAttributeList.Add(pDefAtt.Value)
            Next

            sStatement = "CREATE TABLE " + sTableName + " ("
            For Each pAttribute In pAttributeList
                sStatement += pAttribute.Name + " " + pAttribute.Type + ", "
            Next
            sStatement = sStatement.Substring(0, sStatement.Length - 2)
            sStatement += ")"

            Command.CommandText = sStatement
            Return (Command.ExecuteNonQuery() = 0)
        End Function
        Public Function CreateTable(ByVal pDbItem As IDbItem) As Boolean
            Dim pAttList As New List(Of clsAttribute)
            For Each sKey In pDbItem.Attributes.Keys
                pAttList.Add(pDbItem.Attributes(sKey))
            Next
            Return CreateTable(pDbItem.TableName, pAttList)
        End Function


        ''' <summary>
        ''' Gibt an, ob die Tabelle existiert.
        ''' </summary>
        ''' <param name="sTableName"></param>
        ''' <returns></returns>
        ''' <remarks></remarks>
        Public Function TableExists(ByVal sTableName As String) As Boolean
            Command.CommandText = "SELECT name FROM sqlite_master WHERE (type='table' AND name='" + sTableName + "')"

            If Command.ExecuteScalar = sTableName Then
                Return True
            End If

            Return False
        End Function


        'Private Function Insert(ByVal sTable As String, ByVal pValueList As List(Of Object)) As Boolean
        '    Dim sStatement As String = String.Empty
        '    sStatement = "INSERT INTO " + sTable + " VALUES ( "
        '    For Each pValue As Object In pValueList
        '        Select Case pValue.GetType
        '            Case GetType(String)
        '                sStatement += """" + pValue.ToString + """, "
        '                'Case GetType(Boolean)
        '                '    sStatement += pValue.ToString + ", "
        '                'Case GetType(Integer)
        '                '    sStatement += pValue.ToString + ", "
        '                'Case GetType(Double)
        '                '    sStatement += pValue.ToString + ", "
        '            Case Else
        '                sStatement += pValue.ToString + ", "
        '        End Select
        '    Next

        '    sStatement = sStatement.Substring(0, sStatement.Length - 2)
        '    sStatement += ")"

        '    Command.CommandText = sStatement
        '    Return (Command.ExecuteNonQuery() = 1)
        'End Function
        Public Function Insert(ByVal sTable As String, ByVal pAttributes As List(Of clsAttribute)) As Boolean
            If Not TableExists(sTable) Then
                CreateTable(sTable, pAttributes)
            End If

            Dim bDefaultAttExists As Boolean = False
            Dim pDefaultAttributes As Dictionary(Of String, clsAttribute) = DefaultFields
            For Each sDefaultAttName In pDefaultAttributes.Keys
                bDefaultAttExists = False
                For Each pAtt In pAttributes
                    If pAtt.Name = sDefaultAttName Then
                        bDefaultAttExists = True

                        Select Case sDefaultAttName
                            Case "CreationDate"
                                pAtt.Value = Now()

                            Case "ModificationDate"
                                pAtt.Value = Now()

                        End Select

                        Exit For
                    End If
                Next

                If Not bDefaultAttExists Then
                    Select Case sDefaultAttName
                        Case "CreationDate"
                            pDefaultAttributes(sDefaultAttName).Value = Now()

                        Case "ModificationDate"
                            pDefaultAttributes(sDefaultAttName).Value = Now()
                    End Select

                    pAttributes.Add(pDefaultAttributes(sDefaultAttName))
                End If

            Next
            
            Dim sStatement As String = String.Empty
            sStatement = "INSERT INTO " + sTable + " ("

            ' Adding the Columnnames
            For Each pAttribute As clsAttribute In pAttributes
                sStatement += """" + pAttribute.Name.ToString + """, "
            Next
            sStatement = sStatement.Substring(0, sStatement.Length - 2)
            sStatement += ") VALUES ("

            ' Adding the Columnvalues
            For Each pAttribute As clsAttribute In pAttributes
                Select Case pAttribute.Value.GetType
                    Case GetType(String)
                        sStatement += """" + pAttribute.Value.ToString + """, "
                    Case GetType(DateTime)
                        sStatement += """" + CType(pAttribute.Value, DateTime).ToString("yyyy-MM-dd HH:mm:ss") + """, "
                    Case Else
                        sStatement += pAttribute.Value.ToString + ", "
                End Select
            Next
            sStatement = sStatement.Substring(0, sStatement.Length - 2)
            sStatement += ")"

            Command.CommandText = sStatement
            Return (Command.ExecuteNonQuery() = 1)
        End Function
        Public Function Insert(ByVal sTable As String, ByVal pValueDict As Dictionary(Of String, clsAttribute))
            Dim pList As New List(Of clsAttribute)

            For Each sKey As String In pValueDict.Keys
                pList.Add(pValueDict(sKey))
            Next

            Return Insert(sTable, pList)
        End Function
        Public Function Insert(ByVal pDbObject As IDbItem) As Boolean
            If Not TableExists(pDbObject.TableName) Then
                CreateTable(pDbObject)
            End If

            Return Insert(pDbObject.TableName, pDbObject.Attributes)
        End Function



        Public Function GetNewID(ByVal sTable As String) As String
            If Not TableExists(TABLENAME_ID) Then
                CreateIdTable()
            End If

            Command.CommandText = "SELECT TableId, LastId FROM " + TABLENAME_ID + " WHERE TableName = '" + sTable + "'"

            Dim pDataReader As SQLite.SQLiteDataReader = Command.ExecuteReader
            If pDataReader.HasRows Then
                Dim sTableId As String = pDataReader("TableId")
                Dim sLastId As String = pDataReader("LastId")

                pDataReader.Close()

                sLastId = Tools.clsHexFunctions.HexIncrement(sLastId)

                While sLastId.Length < 8
                    sLastId = "0" + sLastId
                End While

                Command.CommandText = "UPDATE " + TABLENAME_ID + " SET LastId='" + sLastId + "' WHERE TableName = '" + sTable + "'"
                Command.ExecuteNonQuery()

                Return sTableId + ":" + sLastId
            Else
                pDataReader.Close()

                Dim sSysId As String = GetNewSysId()
                Dim sNewTableId As String = sSysId.Remove(0, 9)

                Dim pAtts As Dictionary(Of String, clsAttribute) = GetIdAttributes()
                pAtts("ID").Value = sSysId
                pAtts("TableName").Value = sTable
                pAtts("TableId").Value = sNewTableId
                pAtts("LastId").Value = "00000001"

                Insert(TABLENAME_ID, pAtts)

                Return sNewTableId + ":" + "00000001"
            End If
        End Function

        Private Function GetNewSysId() As String
            Return GetNewID(TABLENAME_ID)
        End Function

        Private Sub CreateIdTable()
            Dim pAttributes As New List(Of clsAttribute)
            Dim pAtts As Dictionary(Of String, clsAttribute) = GetIdAttributes()
            ' ID | TableName | TableId | LastId
            pAttributes.Add(New clsAttribute(pAtts("ID"), "00800000:00000001"))
            pAttributes.Add(New clsAttribute(pAtts("TableName"), TABLENAME_ID))
            pAttributes.Add(New clsAttribute(pAtts("TableId"), "00800000"))
            pAttributes.Add(New clsAttribute(pAtts("LastId"), "00000001"))

            CreateTable(TABLENAME_ID, pAttributes)
            Insert(TABLENAME_ID, pAttributes)
        End Sub
        Private Function GetIdAttributes() As Dictionary(Of String, clsAttribute)
            Dim pOut As New Dictionary(Of String, clsAttribute)
            pOut.Add("ID", New clsAttribute("ID", "VARCHAR(17)"))
            pOut.Add("TableName", New clsAttribute("TableName", "VARCHAR(30)"))
            pOut.Add("TableId", New clsAttribute("TableId", "VARCHAR(8)"))
            pOut.Add("LastId", New clsAttribute("LastId", "VARCHAR(8)"))

            Return pOut
        End Function

        Public Function SelectScalar(ByVal sTable As String, ByVal sColumns As String, ByVal sWhere As String) As Object
            Dim pData As Object = Nothing

            If sColumns Is Nothing Or sColumns = String.Empty Then
                sColumns = "*"
            End If

            Command.CommandText = "SELECT " + sColumns + " FROM " + sTable
            If sWhere IsNot Nothing AndAlso Not (sWhere = String.Empty) Then
                Command.CommandText += " WHERE (" + sWhere + ")"
            End If
            pData = Command.ExecuteScalar()

            Return pData
        End Function
        Public Function [Select](ByVal sTable As String, ByVal sColumns As String, ByVal sWhere As String) As SQLiteDataReader
            Dim pDataReader As SQLiteDataReader = Nothing

            If sColumns Is Nothing Or sColumns = String.Empty Then
                sColumns = "*"
            End If

            Command.CommandText = "SELECT " + sColumns + " FROM " + sTable
            If sWhere IsNot Nothing AndAlso Not (sWhere = String.Empty) Then
                Command.CommandText += " WHERE (" + sWhere + ")"
            End If
            pDataReader = Command.ExecuteReader()

            Return pDataReader
        End Function
        Public Function GetDbItems(ByVal pDbItem As IDbItem, ByVal sWhere As String) As List(Of IDbItem)
            If Not TableExists(pDbItem.TableName) Then
                CreateTable(pDbItem)
            End If

            Dim pOutList As New List(Of IDbItem)

            Dim pDataReader As SQLiteDataReader = [Select](pDbItem.TableName, "*", sWhere)
            If pDataReader Is Nothing Then Return Nothing

            Dim pNewItem As IDbItem = Nothing
            While pDataReader.Read
                pNewItem = pDbItem.Clone
                For Each sAttName As String In pDbItem.Attributes.Keys
                    Try
                        pNewItem.SetAttribute(sAttName, pDataReader(sAttName))
                    Catch ex As Exception
                    End Try
                Next
                pOutList.Add(pNewItem)
            End While
            pDataReader.Close

            Return pOutList
        End Function
        'Public Function [Select](ByVal sStatement As String)
        '    Command.CommandText = sStatement
        '    Dim pDatareader As SQLiteDataReader = Command.ExecuteReader()
        '    Dim pDataset As New DataSet()

        '    Return Nothing
        'End Function

#Region " Shared Functions "
        Public Shared Function GetOpenDatabase(ByVal sRequestingAppName As String) As clsDatabase
            If m_pOpenDatabases.ContainsKey(sRequestingAppName) Then
                Return m_pOpenDatabases(sRequestingAppName)
            End If
            Return Nothing
        End Function

        Public Shared Function OpenDatabaseFromFile(ByVal sDatabaseFilePath As String) As clsDatabase
            'If Not IO.File.Exists(sDatabaseFilePath) Then Return Nothing
            Dim pDatabase As New clsDatabase
            pDatabase.m_sConnectionString = "Data Source=" + sDatabaseFilePath + ";Version=3;"
            Return pDatabase
        End Function
        Public Shared Function OpenDatabaseFromFile(ByVal sRequestingAppName As String, ByVal sDatabaseFilePath As String) As clsDatabase
            CloseDatabase(sRequestingAppName)
            m_pOpenDatabases.Add(sRequestingAppName, OpenDatabaseFromFile(sDatabaseFilePath))
            Return m_pOpenDatabases(sRequestingAppName)
        End Function

        Public Shared Function OpenDatabaseInMemory() As clsDatabase
            Dim pDatabase As New clsDatabase
            pDatabase.m_sConnectionString = "Data Source=:memory:;Version=3;New=False;"
            Return pDatabase
        End Function
        Public Shared Function OpenDatabaseInMemory(ByVal sRequestingAppName As String) As clsDatabase
            CloseDatabase(sRequestingAppName)
            m_pOpenDatabases.Add(sRequestingAppName, OpenDatabaseInMemory())
            Return m_pOpenDatabases(sRequestingAppName)
        End Function

        Public Shared Sub CloseDatabase(ByVal sRequestingAppName As String)
            If m_pOpenDatabases.ContainsKey(sRequestingAppName) Then
                m_pOpenDatabases(sRequestingAppName).Connection.Close()
                m_pOpenDatabases.Remove(sRequestingAppName)
            End If
        End Sub
#End Region

    End Class
End Namespace